package Dynamic_programming;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Before;
import org.junit.Test;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description: $
 * @Param: $
 * @return: $
 * @Author 万家欣
 * @Date: 2022/6/28
 * Algorithm
 * @Version 1.0
 */
public class ExcelExport {
    /**
     * 一对多模式子类行转列导出数据（指定子类某一项作为子类一级标题） 子项为数值类型带汇总
     * @param fatTitle 父类显示列
     * @param sonTitle 子类一级标题
     * @param sonTitleTow 子类二级标题
     * @param dataList 数据行 数据格式 a,b,c,List<d>
     * @throws IOException
     */

    public static void outExcel(
            Map<String,Object> fatTitle,
            List<String> sonTitle,
            Map<String,Object> sonTitleTow,
            List<Map<String,Object>> dataList
    ) throws IOException {
        //创建工作薄对象
        XSSFWorkbook workbook = new XSSFWorkbook();//这里也可以设置sheet的Name
        //创建工作表对象
        XSSFSheet sheet = workbook.createSheet();
        //创建单元格空对象
        XSSFCell cell = null;
        //创建合并空对象
        CellRangeAddress region = null;
        //设置标题
        XSSFRow title = sheet.createRow(0);//设置第一行，从零开始
        //设置标题行高
        title.setHeightInPoints(40);
        //统计合并列(父类属性+子类显示属性*子类标题)
        int titleColumn = fatTitle.size()+(sonTitle.size()+1)*sonTitleTow.size()-1;
        for (int i = 0; i <= titleColumn; i++) {
            cell = title.createCell(i);
            cell.setCellValue("测试数据标题");
            cell.setCellStyle(SheetStyle.getStyle(workbook));
        }
        //合并单元格
        region = new CellRangeAddress(0, 0, 0, titleColumn);
        sheet.addMergedRegion(region);
        sheet.setColumnWidth(0,3000);
        //先创建父类标题
        XSSFRow row = sheet.createRow(1);//设置第一行
        //设置第二行
        XSSFRow row1 = sheet.createRow(2);
        row.setHeightInPoints(20);//设置行高
        row1.setHeightInPoints(18);//设置行高
        int i = 0;  // 列数
        //循环添加父类标题
        for (String s : fatTitle.keySet()) {
            //绘制单元格
            cell=row.createCell(i);
            //添加数据
            cell.setCellValue(fatTitle.get(s).toString());
            //添加样式
            cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));
            //设置要合并的单元格样式
            cell = row1.createCell(i);
            cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));
            //合并 (4个参数，分别为起始行，结束行，起始列，结束列)
            region = new CellRangeAddress(1, 2, i, i++);
            sheet.addMergedRegion(region);
            sheet.setColumnWidth(i,3000);
        }
        //循环添加子类一级标题
        int i_1=i;  // 子类一级标题起始列
        for (String s : sonTitle){
            //绘制单元格
            cell=row.createCell(i_1);
            //添加数据
            cell.setCellValue(s);
            //添加样式
            cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));
            //设置要合并的单元格样式
            cell = row.createCell(i_1+1);
            cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));
            //合并单元格
            region = new CellRangeAddress(1, 1,i_1, i_1+sonTitleTow.size()-1);
            sheet.addMergedRegion(region);
            //循环添加子类二级表头
            int i_2=i_1;  // 子类二级标题起始列
            for (String s1 : sonTitleTow.keySet()){
                cell = row1.createCell(i_2);
                cell.setCellValue(sonTitleTow.get(s1).toString());
                cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));
                sheet.setColumnWidth(i_2,2500);
                i_2++;
            }
            i_1 = i_1+sonTitleTow.size();
        }
        //添加汇总标题
        int i_3 = i_1;
        for (String s1 : sonTitleTow.keySet()){
            cell = row.createCell(i_3);
            cell.setCellValue(sonTitleTow.get(s1).toString()+"汇总");
            cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));
            //设置要合并的单元格样式
            cell = row1.createCell(i_3);
            cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));
            //合并单元格
            region = new CellRangeAddress(1, 2,i_3, i_3++);
            sheet.addMergedRegion(region);
        }
        //循环添加数据
        for (int j = 0; j < dataList.size(); j++) {
            //创建单元格对象
            //设置数据第一行
            XSSFRow row2 = sheet.createRow(j+3);
            row2.setHeightInPoints(18);//设置行高
            //添加父类数据
            int dataCol = 0;
            for (String s : fatTitle.keySet()) {
                cell= row2.createCell(dataCol);
                cell.setCellValue(dataList.get(j).get(s).toString());
                cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));
                dataCol++;
            }
            //添加子类数据
            List<Map> lis = (List<Map>) dataList.get(j).get("porList");
            //根据二级子标题创建汇总集合
            Map<String,Object> sumMap = sonTitleTow;
            for (Map map : lis) {
                for (String s : sonTitle) {
                    //查找和标题行对应的数据
                    if(map.get(s)!=null){
                        for (String str : sonTitleTow.keySet()) {
                            cell = row2.createCell(dataCol);
                            cell.setCellValue( Double.valueOf(map.get(str).toString()));
                            cell.setCellStyle(SheetStyle.getNumCellStyle(workbook));
                            try{
                                sumMap.put(str,Double.valueOf(sumMap.get(str).toString())+Double.valueOf(map.get(str).toString()));
                            }catch (NumberFormatException e){
                                sumMap.put(str,Double.valueOf(map.get(str).toString()));
                            }
                            dataCol++;
                        }
                        break;
                    }
                }
            }
            /**
             * 添加汇总数据
             */
            for (String str : sonTitleTow.keySet()) {
                cell = row2.createCell(dataCol);
                cell.setCellValue(Double.valueOf(sumMap.get(str).toString()));
                cell.setCellStyle(SheetStyle.getNumCellStyle(workbook));
                dataCol++;
            }
        }
        workbook.setSheetName(0, "测试导出");//设置sheet的Name
        //文档输出
        FileOutputStream out = new FileOutputStream("D:/excel/原生poi.xlsx");
        workbook.write(out);
        out.close();
    }



    /**
     * 1、创建工作薄对象
     * 2、创建工作表（sheet）对象
     * 3、创建行
     * 4、创建单元格
     * 5、填充数据
     * 6、设置格式
     * 7、合并单元格
     * @throws IOException
     */

}
